"""
飞检数据STEP8筛选 - output模块
============================================================================

将 VLT2 的数据输出到指定的文件夹中

主要功能：
1. 在指定目录下创建 output/{hspAbbr} 命名的子目录
2. 查询 VLT2 表中医院与 RULE_ID 的去重，制作任务清单
3. 查询 RULE_FLATTEN，KEY like description，查 rule_id 和 value，制作成键是ruleId值是description字典
4. 逐条处理任务清单，输出 EXCEL 文件，文件名是 {hspAbbr}_{ruleId}_{description}.xlsx，输出到目录
============================================================================
"""

import pandas as pd
import sys
import os
import re

sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
from config import create_db_engine

# 创建数据库引擎
engine = create_db_engine()
from sqlalchemy import text

import time


# 时间函数
t0 = time.time()
def elapsed() -> str:
    """返回自脚本启动以来的耗时 (HH:MM:SS)。
    用于将原先的 [INFO]/[START]/[DONE] 等等级标签替换成实时耗时。
    """
    
    # 实际时间，yyy-mm-dd hh:mm:ss
    timeStr = time.strftime("%H:%M:%S", time.localtime())
    
    # 已消耗时间 XX hours XX minutes XX.XX seconds
    delta = int(time.time() - t0)
    if delta < 60:
        return f"{timeStr} (+ {delta} sec)"
    elif delta < 3600:
        m, s = divmod(delta, 60)
        return f"{timeStr} (+ {m} min {s} sec)"
    elif delta < 86400:
        h, rem = divmod(delta, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {h} hour {m} min {s} sec)"
    else:
        d, rem = divmod(delta, 86400)
        h, rem = divmod(rem, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {d} day {h} hour {m} min {s} sec)"

# ============ 
# step1: 查询任务清单
# ============
def fetch_task_list(engine):
    query = """
    SELECT DISTINCT hsp_abbr, rule_id
    FROM vlt
    ORDER BY hsp_abbr, rule_id
    """
    taskDf = pd.read_sql(query, engine)
    # 转换为列表
    taskList = taskDf.to_dict(orient='records')
    print(f"[{elapsed()}] Fetched {len(taskList)} tasks")
    return taskList


# ============ 
# step2: 创建输出目录
# ============
def create_output_dirs(base_dir, hsp_abbrs):
    for hsp_abbr in hsp_abbrs:
        dir_path = os.path.join(base_dir, 'output', hsp_abbr)
        os.makedirs(dir_path, exist_ok=True)
        print(f"[{elapsed()}] Created directory: {dir_path}")


# ============ 
# step3: 制作规则描述 ID
# ============
def fetch_rule_descriptions(engine):
    query = f"""
    SELECT rule_id, value AS description
    FROM rule_flatten
    WHERE key like '%description%'
    """
    ruleDf = pd.read_sql(query, engine)
    ruleDict = pd.Series(ruleDf.description.values, index=ruleDf.rule_id).to_dict()
    print(f"[{elapsed()}] Fetched {len(ruleDict)} rule descriptions")
    return ruleDict

# ============ 
# step4: 逐条处理任务清单，输出 EXCEL 文件
# ============

ILLEGAL_CHAR_PATTERN = re.compile(r"[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]")

def _sanitize_dataframe(df: pd.DataFrame) -> pd.DataFrame:
    """Strip characters Excel/openpyxl forbids so worksheet writes do not fail."""
    if df is None or df.empty:
        return df

    object_cols = df.select_dtypes(include=["object"]).columns
    for col in object_cols:
        df[col] = df[col].map(
            lambda val: ILLEGAL_CHAR_PATTERN.sub("", val) if isinstance(val, str) else val
        )
    return df

def _output_single_task(engine, task, ruleDict, output_file):
    hsp_abbr = task['hsp_abbr']
    rule_id = task['rule_id']
    
    sql = text(f"""
        SELECT 
          vlt.setl_id as 结算ID,
          vs.人员编号,
          vs.人员姓名,
          vs.人员证件类型,
          vs.证件号码,
          vs.性别,
          vs.出生日期,
          vs.年龄,
          vs.险种类型,
          vs.人员类别,
          vs.定点医药机构编号,
          vs.定点医药机构名称,
          vs.开始日期,
          vs.结束日期,
          vs.结算时间,
          vs.医疗类别,
          vmd."住院/门诊号",
          vmd.入院科室名称,
          vmd.出院科室名称,
          vmd.离院方式,
          vmd.病种编号,
          vmd.病种名称,
          vmd.门诊诊断信息,
          vmd.入院科室名称,
          vmd.出院科室名称,
          vlt.dxmaincode as 主要诊断代码,
          vlt.dxmainname as 主要诊断名称,
          vlt.dxcodeall as 全诊断编码,
            vlt.dxnameall as 全诊断名称,
            vlt.txmaincode as 主要手术操作代码,
            vlt.txmainname as 主要手术操作名称,
            vlt.txcodeall as 全手术操作编码,
            vlt.txnameall as 全手术操作名称,
            vlt.item_code as 医保项目编码,
            vlt.item_name as 医保项目名称,
            vlt.item_med_code as 医疗目录编码,
               vlt.item_hsp_code as 医院项目编码,
               vlt.item_hsp_name as 医院项目名称,
               vlt.item_dept as 开单科室,
               -- vlt.apply_dept as 执行科室,
               vlt.item_time as 费用发生时间,
               vlt.p as 单价,
               vlt.q as 数量,
               vlt.c as 明细项目金额,
               vlt.b as 明细医保内金额,
               vlt.item_code2 as 相关医保项目编码,
               vlt.item_name2 as 相关医保项目名称,
               vlt.item_med_code2 as 相关医疗目录编码,
                vlt.item_hsp_code2 as 相关医院项目编码,
                vlt.item_hsp_name2 as 相关医院项目名称,
                vlt.item_dept2 as 相关开单科室,
               -- vlt.apply_dept2 as 相关执行科室,
                vlt.item_time2 as 相关费用发生时间,
                vlt.p2 as 相关单价,
                vlt.q2 as 相关数量,
                vlt.c2 as 相关明细项目金额,
                vlt.b2 as 相关明细医保内金额,
               vlt.vlt_q as 违规数量,
               vlt.vlt_p as 违规单价,
               vlt.vlt_c as 违规金额,
                vlt.vlt_b as 违规医保内金额,
               vlt.related as 备注信息,
               vlt.rule_id as 规则ID,
               vlt.rule_act_id as 规则动作ID,
               vlt.rule_act_msg as 规则动作信息
        FROM vlt2 vlt, v_setl_map vs, v_mdtrt_map vmd
        WHERE vlt.hsp_abbr = :hsp_abbr
          AND vlt.rule_id = :rule_id
          AND vlt.setl_id = vs.结算ID
          AND vlt.mdtrt_id = vmd.就诊ID
    """)
    df = pd.read_sql(sql, engine, params={
        'hsp_abbr': hsp_abbr,
        'rule_id': rule_id
    })
    df = _sanitize_dataframe(df)
    # 输出到 Excel
    df.to_excel(output_file, index=False)

def output(engine, taskList, ruleDict, output_base_dir):
    total_tasks = len(taskList)
    for idx, task in enumerate(taskList):
        hsp_abbr = task['hsp_abbr']
        rule_id = task['rule_id']
        description = ruleDict.get(rule_id, '未知规则')
        safe_description = ''.join(c if c.isalnum() or c in (' ', '_') else '_' for c in description)
        output_file = os.path.join(output_base_dir, 'output', hsp_abbr, f"{hsp_abbr}_{rule_id}_{safe_description}.xlsx")
        
        print(f"[{elapsed()}] Processing task {idx + 1}/{total_tasks}: {hsp_abbr}, {rule_id} -> {output_file}")
        
        _output_single_task(engine, task, ruleDict, output_file)
        
        print(f"[{elapsed()}] Completed task {idx + 1}/{total_tasks}")

# ============ 
# 主流程
# ============
if __name__ == "__main__":
    base_dir = os.path.dirname(os.path.abspath(__file__))
    
    # Step 1: 查询任务清单
    taskList = fetch_task_list(engine)
    hspAbbrList = sorted(set(task['hsp_abbr'] for task in taskList))

    # Step 2: 创建输出目录
    create_output_dirs(base_dir, hspAbbrList)
    
    # Step 3: 制作规则描述 ID
    ruleDict = fetch_rule_descriptions(engine)
    
    # Step 4: 逐条处理任务清单，输出 EXCEL 文件
    output(engine, taskList, ruleDict, base_dir)
    
    print(f"[{elapsed()}] All tasks completed.")